﻿using PachonDemo;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Until;

namespace pachon
{
    public class pcDAL
    {
        //DbHelper dbh = new DbHelper("Data Source=UDDB120;user=UDEMPTY;password=UDEMPTY;");
        DbHelper dbh = new DbHelper();


        /// <summary>
        /// 获取爬去过的网址地址
        /// </summary>
        /// <returns></returns>
        public List<string> GetPC_CONTENTRunedUrl()
        {
            string strsql = "select weburl from PC_CONTENT ";
            List<string> listStrUrl = new List<string>();
            DbDataReader reader = dbh.ExecuteReader(strsql);
            while (reader.Read())
            {
                string webUrl = null;
                webUrl = reader["weburl"] == null ? null : reader["weburl"].ToString();
                listStrUrl.Add(webUrl);
            }
            return listStrUrl;
        }

        /// <summary>
        /// 爬虫对象网址和入口网址
        /// </summary>
        /// <returns></returns>
        public List<PC_INWEB> GetPC_INWEB()
        {
            string strsql = "select * from PC_INWEB";
            List<PC_INWEB> lpc_inweb = new List<PC_INWEB>();
            DbDataReader reader = dbh.ExecuteReader(strsql);
            while (reader.Read())
            {
                PC_INWEB pc_i = new PC_INWEB();
                pc_i.id = reader["id"] == null ? null : reader["id"].ToString();// 编号
                pc_i.cando = reader["cando"] == null ? "0" : reader["cando"].ToString();//是否可执行爬虫计划
                pc_i.weburl = reader["weburl"] == null ? null : reader["weburl"].ToString();// 网址
                pc_i.inweburl = reader["inweburl"] == null ? null : reader["inweburl"].ToString();// 入口
                pc_i.webname = reader["webname"] == null ? null : reader["webname"].ToString();// 网站名
                pc_i.endcode = reader["endcode"] == null ? null : reader["endcode"].ToString();// 网站的编码格式
                pc_i.webregex1 = reader["webregex1"] == null ? null : reader["webregex1"].ToString();// 获取列正则
                pc_i.webregex2 = reader["webregex2"] == null ? null : reader["webregex2"].ToString();// 获取列正则
                pc_i.webregex3 = reader["webregex3"] == null ? null : reader["webregex3"].ToString();//获取列正则
                pc_i.pccity = reader["pccity"] == null ? null : reader["pccity"].ToString();//城市
                pc_i.pctype = reader["pctype"] == null ? null : reader["pctype"].ToString();//类别
                pc_i.appendurl = reader["appendurl"] == null ? null : reader["appendurl"].ToString();//拼接网址
                pc_i.creatertime = baseTime.DateIsNullorEmpty(reader["creatertime"]) == true ? baseTime.ConverToDateTime(reader["creatertime"]) : baseTime.ConverToDateTime(reader["creatertime"]);// 创建正则的时间
                pc_i.uddatetime = baseTime.DateIsNullorEmpty(reader["uddatetime"]) == true ? baseTime.ConverToDateTime(reader["uddatetime"]) : baseTime.ConverToDateTime(reader["uddatetime"]);// 修改正则的时间
                lpc_inweb.Add(pc_i);
            }
            return lpc_inweb;
        }

        public int UpdataPC_INWEB(PC_INWEB pci)
        {
            string strsql = "update pc_inweb set weburl=:weburl,inweburl=:inweburl,webname=:webname,endcode=:endcode,cando=:cando,webregex1=:webregex1,webregex2=:webregex2,webregex3=:webregex3,uddatetime=:uddatetime,pccity=:pccity,pctype=:pctype,appendurl=:appendurl where id=:id ";
            OracleParameter[] op = new OracleParameter[] 
            {
            new OracleParameter (":weburl",OracleType.VarChar),
            new OracleParameter (":inweburl", OracleType.VarChar),
            new OracleParameter (":webname",OracleType.VarChar),
            new OracleParameter (":endcode", OracleType.VarChar),
            new OracleParameter (":cando", OracleType.VarChar),
            new OracleParameter (":webregex1", OracleType.VarChar),
            new OracleParameter (":webregex2", OracleType.VarChar),
            new OracleParameter (":webregex3", OracleType.VarChar),

       

            new OracleParameter (":uddatetime", OracleType.DateTime),
            
            new OracleParameter (":pccity", OracleType.VarChar),
            new OracleParameter (":pctype", OracleType.VarChar),
            new OracleParameter (":appendurl", OracleType.VarChar),

            new OracleParameter (":id", OracleType.VarChar),
           
            };

            op[0].Value = pci.weburl;
            op[1].Value = pci.inweburl;
            op[2].Value = pci.webname;
            op[3].Value = pci.endcode;
            op[4].Value = pci.cando;
            op[5].Value = pci.webregex1;
            op[6].Value = pci.webregex2;
            op[7].Value = pci.webregex3;
            op[8].Value = pci.uddatetime;
            op[9].Value = pci.pccity;
            op[10].Value = pci.pctype;
            op[11].Value = pci.appendurl;
            op[12].Value = pci.id;

            return dbh.ExecuteNonQuery(strsql, op);
        }

        public int InsertPC_INWEB(PC_INWEB pci)
        {
            string strsql = "insert into pc_inweb(id,weburl,inweburl,webname,endcode,cando,webregex1,creatertime,uddatetime,webregex2,webregex3,pccity,pctype,appendurl) values(:id,:weburl,:inweburl,:webname,:endcode,:cando,:webregex1,:creatertime,:uddatetime,:webregex2,:webregex3,:pccity,:pctype,:appendurl)";
            OracleParameter[] op = new OracleParameter[] 
            {
            new OracleParameter (":id", OracleType.VarChar),
            new OracleParameter (":weburl",OracleType.VarChar),
            new OracleParameter (":inweburl", OracleType.VarChar),
            new OracleParameter (":webname",OracleType.VarChar),
            new OracleParameter (":cando", OracleType.VarChar),
            new OracleParameter (":endcode", OracleType.VarChar),
            new OracleParameter (":webregex1", OracleType.VarChar),
            new OracleParameter (":webregex2", OracleType.VarChar),
            new OracleParameter (":webregex3", OracleType.VarChar),
            new OracleParameter (":creatertime", OracleType.DateTime),
            new OracleParameter (":uddatetime", OracleType.DateTime),
            new OracleParameter (":pccity", OracleType.VarChar),
            new OracleParameter (":pctype", OracleType.VarChar),
            new OracleParameter (":appendurl", OracleType.VarChar),
            };

            op[0].Value = pci.id;
            op[1].Value = pci.weburl;
            op[2].Value = pci.inweburl;
            op[3].Value = pci.webname;
            op[4].Value = pci.cando;
            op[5].Value = pci.endcode;
            op[6].Value = pci.webregex1;
            op[7].Value = pci.webregex2;
            op[8].Value = pci.webregex3;
            op[9].Value = pci.creatertime;
            op[10].Value = pci.uddatetime;
            op[11].Value = pci.pccity;
            op[12].Value = pci.pctype;
            op[13].Value = pci.appendurl;
            return dbh.ExecuteNonQuery(strsql, op);
        } 

        /// <summary>
        /// 网页内容正则表达式
        /// </summary>
        /// <returns></returns>
        public List<PC_CONTENT_REGEX> GetPC_CONTENT_REGEX()
        {
            string strsql = "select * from PC_CONTENT_REGEX";
            List<PC_CONTENT_REGEX> lpccr = new List<PC_CONTENT_REGEX>();
            DbDataReader reader = dbh.ExecuteReader(strsql);
            while (reader.Read())
            {
                PC_CONTENT_REGEX pccr = new PC_CONTENT_REGEX();		
                pccr.id = reader["id"] == null ? null : reader["id"].ToString();                        //编号
                pccr.weburl_id = reader["weburl_id"] == null ? null : reader["weburl_id"].ToString();          //网址ID

                pccr.title_regex1 = reader["title_regex1"] == null ? null : reader["title_regex1"].ToString();    //标题正则1
                pccr.title_regex2 = reader["title_regex2"] == null ? null : reader["title_regex2"].ToString();    //标题正则2   
                pccr.title_regex3 = reader["title_regex3"] == null ? null : reader["title_regex3"].ToString();    //标题正则3

                pccr.time_regex1 = reader["time_regex1"] == null ? null : reader["time_regex1"].ToString();      //时间正则1
                pccr.time_regex2 = reader["time_regex2"] == null ? null : reader["time_regex2"].ToString();      //时间正则2
                pccr.time_regex3 = reader["time_regex3"] == null ? null : reader["time_regex3"].ToString();      //时间正则3

                pccr.content_regex1 = reader["content_regex1"] == null ? null : reader["content_regex1"].ToString();//内容正则1
                pccr.content_regex2 = reader["content_regex2"] == null ? null : reader["content_regex2"].ToString();//内容正则2
                pccr.content_regex3 = reader["content_regex3"] == null ? null : reader["content_regex3"].ToString();//内容正则3
                lpccr.Add(pccr);
            }
            return lpccr;
        }

        public int UpdatePC_CONTENT_REGEX(PC_CONTENT_REGEX pc_c_r)
        {
            string strsql="update pc_content_regex set title_regex1=:title_regex1,title_regex2=:title_regex2,title_regex3=:title_regex3,time_regex1=:time_regex1,time_regex2=:time_regex2,time_regex3=:time_regex3,content_regex1=:content_regex1,content_regex2=:content_regex2, content_regex3=:content_regex3 where weburl_id=:weburl_id";

            OracleParameter[] op = new OracleParameter[] 
            {
            new OracleParameter(":title_regex1", OracleType.VarChar),
            new OracleParameter(":title_regex2", OracleType.VarChar),
            new OracleParameter(":title_regex3", OracleType.VarChar),
            new OracleParameter(":time_regex1", OracleType.VarChar),
            new OracleParameter(":time_regex2", OracleType.VarChar),
            new OracleParameter(":time_regex3", OracleType.VarChar),
            new OracleParameter(":content_regex1", OracleType.VarChar),
            new OracleParameter(":content_regex2", OracleType.VarChar),
            new OracleParameter(":content_regex3", OracleType.VarChar),

            new OracleParameter(":weburl_id", OracleType.VarChar),
            };

            op[0].Value = pc_c_r.title_regex1;
            op[1].Value = pc_c_r.title_regex2;
            op[2].Value = pc_c_r.title_regex3;
            op[3].Value = pc_c_r.time_regex1;
            op[4].Value = pc_c_r.time_regex2;
            op[5].Value = pc_c_r.time_regex3;
            op[6].Value = pc_c_r.content_regex1;
            op[7].Value = pc_c_r.content_regex2;
            op[8].Value = pc_c_r.content_regex3;
            op[9].Value = pc_c_r.weburl_id;

            return dbh.ExecuteNonQuery(strsql, op);
        }


        public int InsertPC_CONTENT_REGEX(PC_CONTENT_REGEX pc_c_r)
        {
            string strsql = "insert into pc_content_regex(id,weburl_id,title_regex1,time_regex1,content_regex1,title_regex2,time_regex2,content_regex2,title_regex3,time_regex3,content_regex3) values(:id,:weburl_id,:title_regex1,:time_regex1,:content_regex1,:title_regex2,:time_regex2,:content_regex2,:title_regex3,:time_regex3,:content_regex3)";
            OracleParameter[] op = new OracleParameter[] 
            {
              new OracleParameter(":id", OracleType.VarChar),
              new OracleParameter(":weburl_id", OracleType.VarChar), 
              new OracleParameter(":title_regex1", OracleType.VarChar), 
              new OracleParameter(":time_regex1", OracleType.VarChar),
              new OracleParameter(":content_regex1", OracleType.VarChar), 
              new OracleParameter(":title_regex2", OracleType.VarChar), 
              new OracleParameter(":time_regex2", OracleType.VarChar), 
              new OracleParameter(":content_regex2", OracleType.VarChar), 
              new OracleParameter(":title_regex3", OracleType.VarChar), 
              new OracleParameter(":time_regex3", OracleType.VarChar), 
              new OracleParameter(":content_regex3", OracleType.VarChar),
            };

            op[0].Value = pc_c_r.id;
            op[1].Value = pc_c_r.weburl_id;
            op[2].Value = pc_c_r.title_regex1;
            op[3].Value = pc_c_r.time_regex1;
            op[4].Value = pc_c_r.content_regex1;
            op[5].Value = pc_c_r.title_regex2;
            op[6].Value = pc_c_r.time_regex2;
            op[7].Value = pc_c_r.content_regex2;
            op[8].Value = pc_c_r.title_regex3;
            op[9].Value = pc_c_r.time_regex3;
            op[10].Value = pc_c_r.content_regex3;

            return dbh.ExecuteNonQuery(strsql, op);
        }

        /// <summary>
        /// 爬虫的内容存储
        /// </summary>
        /// <returns></returns>
        public List<PC_CONTENT> GetPC_CONTENT()
        {
            string strsql = "select * from PC_CONTENT";
            List<PC_CONTENT> lpc_content = new List<PC_CONTENT>();
            DbDataReader reader = dbh.ExecuteReader(strsql);
            while (reader.Read())
            {
                PC_CONTENT pc_c = new PC_CONTENT();
                pc_c.id = reader["id"] == null ? null : reader["id"].ToString();
                pc_c.weburl = reader["weburl"] == null ? null : reader["weburl"].ToString();
                pc_c.title = reader["title"] == null ? null : reader["title"].ToString();
                pc_c.webid = reader["webid"] == null ? null : reader["webid"].ToString();
                pc_c.content = reader["content"] == null ? null : reader["content"].ToString();
                pc_c.time = reader["time"] == null ? null : reader["time"].ToString();
                pc_c.creattime = baseTime.DateIsNullorEmpty(reader["creattime"]) == true ? baseTime.ConverToDateTime(reader["creattime"]) : baseTime.ConverToDateTime(reader["creattime"]);
                lpc_content.Add(pc_c);
            }
            return lpc_content;
        }


        /// <summary>
        /// 插入内容
        /// </summary>
        /// <returns></returns>
        public void InsertPC_CONTENTList(List<PC_CONTENT> listpmlc)
        {

            string sSql = "";

            List<Until.DbHelper.sttSqlList> lParameterSqlList = new List<Until.DbHelper.sttSqlList>();
            foreach (PC_CONTENT pmlc in listpmlc)
            {
                //将被执行的语句1
                sSql =@"insert into pc_content(id,weburl,title,webid,creattime,content,time) values (:id,:weburl,:title,:webid,sysdate,:content,:time)";
                //语句1参数列表
                Until.DbHelper.sttSqlParameterList[] sPraList = new Until.DbHelper.sttSqlParameterList[6];


                sPraList[0].Name = "id";
                sPraList[0].Type = "string";
                sPraList[0].Value = pmlc.id;

                sPraList[1].Name = "weburl";
                sPraList[1].Type = "string";
                sPraList[1].Value = pmlc.weburl;

                sPraList[2].Name = "title";
                sPraList[2].Type = "string";
                sPraList[2].Value = pmlc.title;

                sPraList[3].Name = "webid";
                sPraList[3].Type = "string";
                sPraList[3].Value = pmlc.webid;

                sPraList[4].Name = "content";
                sPraList[4].Type = "string";
                sPraList[4].Value = pmlc.content;

                sPraList[5].Name = "time";
                sPraList[5].Type = "string";
                sPraList[5].Value =pmlc.time;

                //语句1及其参数列表
                Until.DbHelper.sttSqlList ssLInfo = new Until.DbHelper.sttSqlList();
                ssLInfo.atbSQl = sSql;
                ssLInfo.atbSqlPraList = sPraList;

                //最终要执行的参数语句列表
                //增加语句1及其参数
                lParameterSqlList.Add(ssLInfo);
            }
            //执行事务(传入多条SQL)
            dbh.ExecParameterTransaction(lParameterSqlList);
        }



        /// <summary>
        /// 插入单条内容
        /// </summary>
        /// <returns></returns>
        public void InsertPC_CONTENT(PC_CONTENT pcn)
        {

            string strsql = @"insert into pc_content(id,weburl,title,webid,creattime,content,time) values (:id,:weburl,:title,:webid,sysdate,:content,:time)";

            OracleParameter[] op = new OracleParameter[] 
            {
            new OracleParameter (":id", OracleType.VarChar),
            new OracleParameter (":weburl",OracleType.VarChar),
            new OracleParameter (":title", OracleType.VarChar),
            new OracleParameter (":webid",OracleType.VarChar),
            new OracleParameter (":content", OracleType.VarChar),
            new OracleParameter (":time", OracleType.VarChar)
         
            };

            op[0].Value = pcn.id;
            op[1].Value = pcn.weburl;
            op[2].Value = pcn.title;
            op[3].Value = pcn.webid;
            op[4].Value = pcn.content;
            op[5].Value = pcn.time;

            dbh.ExecuteNonQuery(strsql,op);
        }
    }
}
